package com.clqteam.daoimpl;

import java.awt.FontFormatException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.SelectEmployeeDao;
import com.clqteam.model.Employee;
import com.clqteam.util.JDBCutil;
/**
 * 
 * @author CLQ
 *
 */
public class SelectEmployeeDaoImpl implements SelectEmployeeDao{

	@Override
	public List<Employee> getEmployeeBysk(String sk,int page,int rows) {
		List<Employee> employee=new ArrayList<Employee>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT s_employee.eId,s_employee.eAccount,s_employee.ePsw,s_employee.eName,s_role.rName");
		sql.append(" FROM s_employee INNER JOIN s_role");
		sql.append(" ON s_employee.rId=s_role.rId");
		sql.append(" WHERE s_employee.eName LIKE ? OR");
		sql.append(" s_employee.eAccount LIKE ? OR");
		sql.append(" s_role.rName LIKE ?");
		sql.append(" LIMIT ?,?");
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, "%"+sk+"%");
			pst.setString(2, "%"+sk+"%");
			pst.setString(3, "%"+sk+"%");
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			 ResultSet rs=pst.executeQuery();
			 while(rs.next()){
					Employee emp=new Employee();
					 emp.seteName(rs.getString("eName"));
					 emp.seteAccount(rs.getString("eAccount"));
					 emp.setePsw(rs.getString("ePsw"));
					 emp.setrName(rs.getString("rName"));
					 employee.add(emp);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employee;
	}

	@Override
	    public int getEmployeeCountByeId(String sk) {
		int count=0;
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT COUNT(eId)as ecount FROM s_employee");
		sql.append(" INNER JOIN s_role");
		sql.append(" ON s_employee.rId=s_role.rId");
		sql.append(" WHERE s_employee.eName LIKE ? OR");
		sql.append(" s_employee.eAccount LIKE ? OR");
		sql.append(" s_role.rName LIKE ?");
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, "%"+sk+"%");
			pst.setString(2, "%"+sk+"%");
			pst.setString(3, "%"+sk+"%");
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
			count=rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

}
